﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using VideoClassLibrary.Model;

namespace VideoClassLibrary.BusinessLogicLayer
{
   public class StudentModule
    {
        /// <summary>
        /// MD5加密
        /// </summary>
        /// <returns></returns>
        public static string MD5Enctrypt(string pwd)
        {
            return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(pwd, "md5");
        }

        /// <summary>
        /// 是否可以注册以参数loginName为登录名的用户
        /// </summary>
        /// <param name="loginName">待检验的登录名</param>
        /// <returns>可以注册为true,否则为false</returns>
        public bool CanRegistUser(string loginName)
        {
            string strSql = "select count(1) FROM V_Student where Stu_LoginName=@loginName";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql);
            db.AddInParameter(command, "loginName", DbType.AnsiString, loginName);
            return !(int.Parse(db.ExecuteScalar(command).ToString()) > 0);
        }

        public void UpdateStuPasswrod(V_Student stu)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_Student set ");
            strSql.Append("Update_Time=@Update_Time,");
            strSql.Append("Stu_Password=@Stu_Password");
            strSql.Append(" where StuID=@StuID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(cmd, "Update_Time", DbType.DateTime, stu.Update_Time);
            db.AddInParameter(cmd, "Stu_Password", DbType.AnsiString, stu.Stu_Password);
            db.AddInParameter(cmd, "StuID", DbType.Int32, stu.StuID);
            db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 更新学生帐号信息
        /// </summary>
        public void UpdateStudent(V_Student model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_Student set ");
            strSql.Append("Update_Time=@Update_Time,");
            strSql.Append("Create_Time=@Create_Time,");
            strSql.Append("SchoolID=@SchoolID,");
            strSql.Append("RoleID=@RoleID,");
            strSql.Append("Stu_State=@Stu_State,");
            strSql.Append("Stu_Type=@Stu_Type,");
            strSql.Append("Stu_LoginName=@Stu_LoginName,");
            strSql.Append("Stu_Password=@Stu_Password,");
            strSql.Append("Stu_RealName=@Stu_RealName,");
            strSql.Append("Stu_Mobile=@Stu_Mobile,");
            strSql.Append("Stu_Email=@Stu_Email,");
            strSql.Append("MoneyPurseID=@MoneyPurseID,");
            strSql.Append("Entry_Time=@Entry_Time");
            strSql.Append(" where  StuID=@StuID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "StuID", DbType.Int32, model.StuID);
            db.AddInParameter(dbCommand, "Update_Time", DbType.DateTime, model.Update_Time);
            db.AddInParameter(dbCommand, "Create_Time", DbType.DateTime, model.Create_Time);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "RoleID", DbType.Int32, model.RoleID);
            db.AddInParameter(dbCommand, "Stu_State", DbType.Int32, model.Stu_State);
            db.AddInParameter(dbCommand, "Stu_Type", DbType.Int32, model.Stu_Type);
            db.AddInParameter(dbCommand, "Stu_LoginName", DbType.String, model.Stu_LoginName);
            db.AddInParameter(dbCommand, "Stu_Password", DbType.String, model.Stu_Password);
            db.AddInParameter(dbCommand, "Stu_RealName", DbType.String, model.Stu_RealName);
            db.AddInParameter(dbCommand, "Stu_Mobile", DbType.String, model.Stu_Mobile);
            db.AddInParameter(dbCommand, "Stu_Email", DbType.String, model.Stu_Email);
            db.AddInParameter(dbCommand, "MoneyPurseID", DbType.Int32, model.MoneyPurseID);
            db.AddInParameter(dbCommand, "Entry_Time", DbType.DateTime, model.Entry_Time);
            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 增加一条学生信息
        /// </summary>
        public int AddStudent(V_Student model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_Student(");
            strSql.Append("Update_Time,Create_Time,SchoolID,RoleID,Stu_State,Stu_Type,Stu_LoginName,Stu_Password,Stu_RealName,Stu_Mobile,Stu_Email,MoneyPurseID,Entry_Time)");

            strSql.Append(" values (");
            strSql.Append("@Update_Time,@Create_Time,@SchoolID,@RoleID,@Stu_State,@Stu_Type,@Stu_LoginName,@Stu_Password,@Stu_RealName,@Stu_Mobile,@Stu_Email,@MoneyPurseID,@Entry_Time)");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Update_Time", DbType.DateTime, model.Update_Time);
            db.AddInParameter(dbCommand, "Create_Time", DbType.DateTime, model.Create_Time);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "RoleID", DbType.Int32, model.RoleID);
            db.AddInParameter(dbCommand, "Stu_State", DbType.Int32, model.Stu_State);
            db.AddInParameter(dbCommand, "Stu_Type", DbType.Int32, model.Stu_Type);
            db.AddInParameter(dbCommand, "Stu_LoginName", DbType.String, model.Stu_LoginName);
            db.AddInParameter(dbCommand, "Stu_Password", DbType.String, model.Stu_Password);
            db.AddInParameter(dbCommand, "Stu_RealName", DbType.String, model.Stu_RealName);
            db.AddInParameter(dbCommand, "Stu_Mobile", DbType.String, model.Stu_Mobile);
            db.AddInParameter(dbCommand, "Stu_Email", DbType.String, model.Stu_Email);
            db.AddInParameter(dbCommand, "MoneyPurseID", DbType.Int32, model.MoneyPurseID);
            db.AddInParameter(dbCommand, "Entry_Time", DbType.DateTime, model.Entry_Time);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }

       /// <summary>
        ///  学生登陆并且可以记录学生信息
       /// </summary>
       /// <param name="ID"></param>
       /// <returns></returns>
        public V_user userLogin(string name, string pwd)
        {
            V_user user = new V_user();
            string sql = "select [ID],[update_time],[create_time],[SchoolID],[VU_realname],[VU_mobile],[VU_email] from [V_user] where [VU_loginname]=@Aname and [VU_Password]=@Apwd";
            SqlParameter par = new SqlParameter("@Aname", name);
            SqlParameter par1 = new SqlParameter("@Apwd", MD5Enctrypt(pwd));
            VDataBase vb = new VDataBase();
            SqlDataReader read = vb.GetDataByReader(sql, false, par, par1);
            if (read.HasRows)
            {
                read.Read();
                user.ID = Convert.ToInt32(read[0]);
                user.update_time = read[1] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(read[1]);
                user.create_time = read[2] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(read[2]);
                user.SchoolID = Convert.ToInt32(read[3].ToString());
                user.VU_realname = read[4].ToString();
                user.VU_mobile = read[5].ToString();
                user.VU_email = read[6].ToString();
                read.Close();
                return user;
            }
            else
            {
                return null;
            }

        }
        
        /// <summary>
        /// 学生更改密码
       /// </summary>
       /// <param name="ID"></param>
       /// <returns></returns> 
       public int UpdatePwd(string newpwd,int id)
         {
             VDataBase v = new VDataBase();
             string sql = "update V_user set update_time=GETDATE(),VU_Password=@pwd where ID=@id";
             SqlParameter p1 = new SqlParameter("@pwd", newpwd);
             SqlParameter p2 = new SqlParameter("@id", id);
             int i = v.GetDataByNonQuery(sql, false, p1, p2);
             if(i>0)
             { return 1; }
             else
             { return -1; }
         }
       

    }
}